package mytest.wd4;

import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

import static mytest.sql.SqlConstants.*;

public class Test_6 {
    public static void main(String[] args) {
        System.out.println(joinSql("2024-11-25", "0"));
    }

    private static String joinSql(String dateStr, String cycle) {
        DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE;
        DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyyMM");
        LocalDate date = LocalDate.parse(dateStr, formatter);
        String month0 = date.minusMonths(0).format(formatter2);
        String month1 = date.minusMonths(1).format(formatter2);
        String month2 = date.minusMonths(2).format(formatter2);
        LocalDateTime startOfDay = date.atStartOfDay();
        LocalDateTime endOfDay = date.atTime(23, 59, 59);
        ZoneId nigeriaTimeZone = ZoneId.systemDefault();
        Instant startOfDayInstant = startOfDay.atZone(nigeriaTimeZone).toInstant();
        Instant endOfDayInstant = endOfDay.atZone(nigeriaTimeZone).toInstant();

        String startOfDayTimestampSec = String.valueOf(startOfDayInstant.getEpochSecond());
        String endOfDayTimestampSec = String.valueOf(endOfDayInstant.getEpochSecond());
        String startOfDayTimestampMs = String.valueOf(startOfDayInstant.toEpochMilli());
        String endOfDayTimestampMs = String.valueOf(endOfDayInstant.toEpochMilli());

        StringBuilder s1 = new StringBuilder();
        StringBuilder s2 = new StringBuilder();
        StringBuilder s3 = new StringBuilder();

        s1.append(String.format(INSERT_PERFORMANCE_REPORT_SQL_1, month0, startOfDayTimestampSec, endOfDayTimestampSec)).append("\nunion all\n")
                .append(String.format(INSERT_PERFORMANCE_REPORT_SQL_1, month1, startOfDayTimestampSec, endOfDayTimestampSec)).append("\nunion all\n")
                .append(String.format(INSERT_PERFORMANCE_REPORT_SQL_1, month2, startOfDayTimestampSec, endOfDayTimestampSec)).append("\n");

        s2.append(String.format(INSERT_PERFORMANCE_REPORT_SQL_2, month0)).append("\nunion all\n")
                .append(String.format(INSERT_PERFORMANCE_REPORT_SQL_2, month1)).append("\nunion all\n")
                .append(String.format(INSERT_PERFORMANCE_REPORT_SQL_2, month2)).append("\n");

        s3.append(String.format(INSERT_PERFORMANCE_REPORT_SQL_3, month0, startOfDayTimestampMs, endOfDayTimestampMs, cycle)).append("\nunion all\n")
                .append(String.format(INSERT_PERFORMANCE_REPORT_SQL_3, month1, startOfDayTimestampMs, endOfDayTimestampMs, cycle)).append("\nunion all\n")
                .append(String.format(INSERT_PERFORMANCE_REPORT_SQL_3, month2, startOfDayTimestampMs, endOfDayTimestampMs, cycle)).append("\n");
        String sql = String.format(PERFORMANCE_REPORT_SQL_1, cycle, dateStr) + s1 + PERFORMANCE_REPORT_SQL_2 + s2 + PERFORMANCE_REPORT_SQL_3 + s3 + PERFORMANCE_REPORT_SQL_4;
        return sql;
    }
}
